package com.jiruan.invoicemanagementsystem.dao.Imp;

import com.jiruan.invoicemanagementsystem.dao.IInvoicingRecordDao;
import com.jiruan.invoicemanagementsystem.pojo.BaseData;
import com.jiruan.invoicemanagementsystem.pojo.InvoicingRecord;
import com.jiruan.invoicemanagementsystem.util.DBHelper;
import com.jiruan.invoicemanagementsystem.util.PageHelper;
import com.jiruan.invoicemanagementsystem.vo.InvoicingRecordDetail;
import com.jiruan.invoicemanagementsystem.vo.InvoicingRecordShow;
import com.jiruan.invoicemanagementsystem.vo.StatisticalInfoShow;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class IInvoicingRecordDaoImp implements IInvoicingRecordDao {


    @Override
    // 获得开票集合
    public ArrayList<InvoicingRecord> getTotalInvoicingRecord(){
        String sql="select *from invoicingrecord ";
        return DBHelper.currencySqlQuery(InvoicingRecord.class,sql);
    }




    //获得类型数据条数
        public int getCountOfInvoicingRecord( InvoicingRecordShow invoicingRecord, Double min, Double max){
            StringBuilder sql=new StringBuilder("select count(*) from invoicingrecord inv left join basedata bd ON inv.baseDataId =bd.id where 1=1  ");

            ArrayList<Object> list=new ArrayList<>();

            if (invoicingRecord.getCreateTime()!=null){
                sql.append(" and DATE(createTime) = DATE(?)");
                list.add(invoicingRecord.getCreateTime());
            }if (invoicingRecord.getStatus()!=null  && !invoicingRecord.getStatus().trim().isEmpty()){
                sql.append(" and status=?");
                list.add(invoicingRecord.getStatus()  );
            }if (invoicingRecord.getTitle()!=null && !invoicingRecord.getTitle().trim().isEmpty()){
                sql.append(" and title like concat('%',?,'%')");
                list.add(invoicingRecord.getTitle());
            }if (min != null) {
                sql.append(" and amount >= ?");
                list.add(min);
            }
            if (max != null) {
                sql.append(" and amount <= ?");
                list.add(max);
            }


            return DBHelper.currencySqlQuery(sql.toString(),list.toArray());

    }


    //分页查询获得数据
    @Override
    public ArrayList<InvoicingRecordShow> selectInvoicingRecordPage( InvoicingRecordShow invoicingRecord, PageHelper ph, Double min, Double max) {
        //查询总条数输出语句
        StringBuilder sql = new StringBuilder("select * from  invoicingrecord inv left join basedata bd ON inv.baseDataId = bd.id where 1=1");
        ArrayList list=new ArrayList<>();
            if (invoicingRecord.getCreateTime()!=null){
                sql.append(" and DATE(createTime) = DATE(?)");
                list.add(invoicingRecord.getCreateTime());
            }if (invoicingRecord.getStatus()!=null  && !invoicingRecord.getStatus().trim().isEmpty()){
                sql.append(" and status=?");
                list.add(invoicingRecord.getStatus()  );
            }if (invoicingRecord.getTitle()!=null && !invoicingRecord.getTitle().trim().isEmpty()){
                sql.append(" and title like concat('%',?,'%')");
                list.add(invoicingRecord.getTitle());
        }if (min != null) {
            sql.append(" and amount >= ?");
            list.add(min);
        }
        if (max != null) {
            sql.append(" and amount <= ?");
            list.add(max);
        }
        sql.append(" limit ?,?");
        int index=(ph.getPageIndex()-1)*ph.getShowNumber();
        list.add(index);
        list.add(ph.getShowNumber());
        return DBHelper.currencySqlQuery(InvoicingRecordShow.class, sql.toString(),list.toArray());

    }


    //根据InvoicingRecord 获得 发票基础信息主键（关联baseData.id）



    //退票操作
    /*
     * 根据"上链地址"找到对应数据
     * 更新对应的开票记录状态(status)为'B' -> 未开票
     * */
    public Integer refundTickById(String upLinkAddress){
        String sql="update invoicingrecord set status='B' where uplinkAddress=?";

        return DBHelper.currencySqlUpdate(sql,upLinkAddress);
    }


    //根据invoicingrecord的uplinkAddress 上链地址 category 发票种类 type获得发票类型    basedata的 enterpriseId 抬头
    // taxNo税号  email的emailDetail邮箱
        public InvoicingRecordDetail queryInvoicingAccordingTo(String upLinkAddress){


        String sql="select null as addressDetail,inv.category,inv.type,bd.taxNo,bd.title,em.emailDetail " +
                "from invoicingrecord inv " +
                "left join basedata bd ON inv.baseDataId = bd.id " +
                "left join email em ON inv.emailId = em.id where inv.uplinkAddress=? " ;


//            // 2. 非空才返回第一个元素，避免抛异常
//            if (detailList.isEmpty()) {
//                System.out.println("Dao层未查询到任何数据");
//                return null;
//            }

        return (InvoicingRecordDetail) DBHelper.currencySqlQuery(InvoicingRecordDetail.class,sql,upLinkAddress).getFirst();
    }


}
